package weaver.interfaces.util;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.general.BaseBean;
import weaver.general.TimeUtil;
import weaver.general.Util;

/**
 * 
 * @ClassName: ExpenseUtil 
 * @Description: TODO
 * @author xiyufei
 * @date 2017-3-16 上午11:20:36 
 *
 */
public  class  ExpenseInfoUtil {
	
	private static String ysxxtable = new BaseBean().getPropValue("expense", "ysxxtable");
	private static String ysxxmodeid = new BaseBean().getPropValue("expense", "ysxxmodeid");
	
	/**
	 * 
	 * @Title: getExpenseAmount1 
	 * @Description: 科室+支出项目+项目
	 * @param @param centitycode
	 * @param @param cexpenseitemcode
	 * @param @param citemcode
	 * @param @return    设定文件 
	 * @return double    返回类型 
	 * @throws
	 */
	public double getExpenseAmount1(String centitycode,String cexpenseitemcode,String citemcode){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select sum(amount) as amount from "+ysxxtable+" where centitycode='"+centitycode+"' and cexpenseitemcode='"+cexpenseitemcode+"' and  citemcode='"+citemcode+"'");
		rsds.next();
		double amount = Util.getDoubleValue(rsds.getString("amount"),0);
		return amount; 
	}
	
	/**
	 * 
	 * @Title: getExpenseAmount2 
	 * @Description: 课题号+支出项目
	 * @param @param cexpenseitemcode
	 * @param @param kth
	 * @param @return    设定文件 
	 * @return double    返回类型 
	 * @throws
	 */
	public double getExpenseAmount2(String cexpenseitemcode,String kth){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select sum(amount) as amount from "+ysxxtable+" where  cexpenseitemcode='"+cexpenseitemcode+"' and  kth='"+kth+"'");
		rsds.next();
		double amount = Util.getDoubleValue(rsds.getString("amount"),0);
		return amount; 
	}
	
	
	/**
	 * 
	 * @Title: getExpenseAmount3 
	 * @Description:课题号总额 
	 * @param @param kth 
	 * @param @return    设定文件 
	 * @return double    返回类型 
	 * @throws
	 */
	public double getExpenseAmount3(String kth){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select sum(amount) as amount from "+ysxxtable+" where kth='"+kth+"'");
		rsds.next();
		double amount = Util.getDoubleValue(rsds.getString("amount"),0);
		return amount; 
	}
	

	/**
	 * 
	 * @Title: getExpenseAmount4 
	 * @Description: 支出项目控制 
	 * @param @param cexpenseitemcode 
	 * @param @return    设定文件 
	 * @return double    返回类型 
	 * @throws
	 */
	public double getExpenseAmount4(String cexpenseitemcode){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select sum(amount) as amount from "+ysxxtable+" where cexpenseitemcode='"+cexpenseitemcode+"'");
		rsds.next();
		double amount = Util.getDoubleValue(rsds.getString("amount"),0);
		return amount; 
	}
	
	/**
	 * 
	 * @Title: getExpenseAmount5 
	 * @Description: 科室+支出项目控制 
	 * @param @param ccode
	 * @param @param cexpenseitemcode
	 * @param @return    设定文件 
	 * @return double    返回类型 
	 * @throws
	 */
	public double getExpenseAmount5(String ccode,String cexpenseitemcode){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select sum(amount) as amount from "+ysxxtable+" where ccode='"+ccode+"' and cexpenseitemcode='"+cexpenseitemcode+"'");
		rsds.next();
		double amount = Util.getDoubleValue(rsds.getString("amount"),0);
		return amount; 
	}
	
	
 
	
	/**
	 * 
	 * @Title: getKsbm 
	 * @Description: 获取科室编码
	 * @param @param ks
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	 */
	public String getKsbm(String ks){
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");  
		rsds.execute("select uf_code from cdept_dz where oa_code='"+ks+"'");
		rsds.next();
		String ksbm = Util.null2String(rsds.getString("uf_code"));
		return ksbm; 
	}

	/**
	 * 
	 * @Title: updateExpense 
	 * @Description: 减少预算
	 * @param @param workflowid
	 * @param @param ccode
	 * @param @param cexpenseitemcode
	 * @param @param citemcode
	 * @param @param kth
	 * @param @param amount
	 * @param @param iyear
	 * @param @return    设定文件 
	 * @return boolean    返回类型 
	 * @throws
	 */
	public boolean subtractExpense(String workflowid, String ccode,String cexpenseitemcode, String citemcode, String kth,double amount, String iyear,String iskt) {
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		RecordSet  rs = new RecordSet(); 
		rsds.execute("select centitycode from EF_EP_V_DEPT where code='"+ccode+"'");
		rsds.next();
		String centitycode  = Util.null2String(rsds.getString("centitycode"));
		String sqlWwhere = "";
		 
		if("1".equals(iskt)){
			sqlWwhere = sqlWwhere+" and (centitycode is null or centitycode ='')"; 
		}else{
			sqlWwhere = sqlWwhere+" and centitycode ='"+centitycode+"'  "; 
		} 
		sqlWwhere = sqlWwhere+" and cexpenseitemcode ='"+cexpenseitemcode+"'  ";

		
		if(!"".equals(citemcode)){
			sqlWwhere = sqlWwhere+" and citemcode ='"+citemcode+"'  ";
		}
		if(!"".equals(kth)){
			sqlWwhere = sqlWwhere+" and citemcode ='"+kth+"'  ";
		}
		if("".equals(citemcode)&&"".equals(kth)){
			sqlWwhere = sqlWwhere+" and  (citemcode is null or citemcode ='') ";
		}
		System.out.println("==跟新=="+"select dcurrentamount from "+ysxxtable+" where iyear='"+iyear+"'  "+sqlWwhere);
		new BaseBean().writeLog("==跟新=="+"select dcurrentamount from "+ysxxtable+" where iyear='"+iyear+"'  "+sqlWwhere);
		rs.execute("select dcurrentamount from "+ysxxtable+" where iyear='"+iyear+"' "+sqlWwhere);
		if(rs.next()){
			String dcurrentamount = Util.null2String(rs.getString("dcurrentamount"));
			String dcurrentamountNew = BigDecimalCalculate.floatSubtract(dcurrentamount, amount+"");
			return rs.execute("update "+ysxxtable+" set dcurrentamount ="+dcurrentamountNew+" where iyear='"+iyear+"' "+sqlWwhere);
		}else{
			return true;
		}
		
		 
	}

/**
 * 	
 * @Title: releaseExpense 
 * @Description: 释放预算
 * @param @param workflowid
 * @param @param ccode
 * @param @param cexpenseitemcode
 * @param @param citemcode
 * @param @param kth
 * @param @param amount
 * @param @param iyear
 * @param @return    设定文件 
 * @return boolean    返回类型 
 * @throws
 */
	public boolean releaseExpense(String workflowid, String ccode,String cexpenseitemcode, String citemcode, String kth,double amount, String iyear,String iskt) {
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		RecordSet  rs = new RecordSet(); 
		rsds.execute("select centitycode from EF_EP_V_DEPT where code='"+ccode+"'");
		rsds.next();
		String centitycode  = Util.null2String(rsds.getString("centitycode"));
		String sqlWwhere = "";
  
		if("1".equals(iskt)){
			sqlWwhere = sqlWwhere+" and  (centitycode is null or citemcode ='') "; 
		}else{
			sqlWwhere = sqlWwhere+" and centitycode ='"+centitycode+"'  "; 
		} 
		
		sqlWwhere = sqlWwhere+" and cexpenseitemcode ='"+cexpenseitemcode+"'  ";
		if(!"".equals(citemcode)){ 
			sqlWwhere = sqlWwhere+" and citemcode ='"+citemcode+"'  ";
		}
		if(!"".equals(kth)){ 
			sqlWwhere = sqlWwhere+" and citemcode ='"+kth+"'  ";
		}
		if("".equals(citemcode)&&"".equals(kth)){
			sqlWwhere = sqlWwhere+" and  (centitycode is null or citemcode ='')";
		}
		rs.execute("select dcurrentamount from "+ysxxtable+" where iyear='"+iyear+"' "+sqlWwhere);
		rs.next();
		String dcurrentamount = Util.null2String(rs.getString("dcurrentamount"));
		String dcurrentamountNew = BigDecimalCalculate.floatAdd(dcurrentamount, amount+""); 
		return rs.execute("update "+ysxxtable+" set dcurrentamount ="+dcurrentamountNew+" where iyear='"+iyear+"' "+sqlWwhere);
	}
	
/**
 * 
 * @Title: getCname 
 * @Description: 获取科室名称
 * @param @param ccode
 * @param @return    设定文件 
 * @return String    返回类型 
 * @throws
 */
	public String getCname(String ccode) {
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select name from EF_EP_V_DEPT where code='"+ccode+"'");
		rsds.next();
		String  cName = Util.null2String(rsds.getString("name"));
		return cName;
	}

	/**
	 * 
	 * @Title: getCItemCode 
	 * @Description: 获取支出项目名称
	 * @param @param cexpenseitemcode
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	 */
	public String getCItemName(String cexpenseitemcode) {
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select  distinct cexpenseitemname from EF_BG_V_ExpenseItemdz where cexpenseitemcode in (select ef_code from jfly_dz where jf_code in ('01','02','03','04','','05','06') )" +
				" and cexpenseitemcode='"+cexpenseitemcode+"'");
		rsds.next();
		String  cexpenseitemname = Util.null2String(rsds.getString("cexpenseitemname"));
		return cexpenseitemname;
	}

	/**
	 * 
	 * @Title: getCtn 
	 * @Description: 获取项目名称
	 * @param @param citemcode
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	 */
	public String getCtn(String citemcode) {
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select  distinct citemname from EF_BG_V_ExpenseItem where citemcode='"+citemcode+"'");
		rsds.next();
		String  citemname = Util.null2String(rsds.getString("citemname"));
		return citemname;
	}
	
	/**
	 * 
	 * @Title: getKthName 
	 * @Description: 获取课题号名称
	 * @param @param kth
	 * @param @return    设定文件 
	 * @return String    返回类型 
	 * @throws
	 */
	public String getKthName(String kth){ 
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		rsds.execute("select distinct citemname from EF_EP_itemdz where jfly='课题' or jfly='临床试验经费' and citemcode='"+kth+"'");
		rsds.next();
		String  citemname = Util.null2String(rsds.getString("citemname"));
		return citemname;
	}

	
	/**
	 * 
	 * @Title: SynExpense 
	 * @Description: 更新预算信息
	 * @param @return    设定文件 
	 * @return boolean    返回类型 
	 * @throws
	 */
	public boolean SynExpense(){
		RecordSet rs = new RecordSet();
		RecordSet rs1 = new RecordSet();
		RecordSetDataSource rsds = new RecordSetDataSource("ZJK");
		boolean bl = false;
		rsds.execute("select iyear,cbudgettype,centitycode,centityname,cexpenseitemcode,cexpenseitemname,citemcode,citemname,dBBGAmount,dsumamount,dcurrentamount from EF_EP_V_EPBudget ");
		while(rsds.next()){
			String iyear = Util.null2String(rsds.getString("iyear"));
			String cbudgettype = Util.null2String(rsds.getString("cbudgettype"));
			String centitycode = Util.null2String(rsds.getString("centitycode"));
			String centityname = Util.null2String(rsds.getString("centityname"));
			String cexpenseitemcode = Util.null2String(rsds.getString("cexpenseitemcode"));	
			String cexpenseitemname = Util.null2String(rsds.getString("cexpenseitemname"));	
			String citemcode = Util.null2String(rsds.getString("citemcode"));	
			String citemname = Util.null2String(rsds.getString("citemname"));	
			String dBBGAmount = Util.null2String(rsds.getString("dBBGAmount"));	
			String dsumamount = Util.null2String(rsds.getString("dsumamount"));	
			String dcurrentamount = Util.null2String(rsds.getString("dcurrentamount"));	
			String type = "";
			String sqlWhere = " iyear ='"+iyear+"' and cbudgettype='"+cbudgettype+"' and centitycode='"+centitycode+"' and cexpenseitemcode='"+cexpenseitemcode+"'";
			if(!"".equals(citemcode)){
				sqlWhere = sqlWhere+" and citemcode='"+citemcode+"'";
			} 
			rs.execute("select id,dBBGAmount,dcurrentamount from "+ysxxtable+" where "+sqlWhere);
			if(rs.next()){
				String dBBGAmount_old = Util.null2String(rs.getString("dBBGAmount"));
				String dcurrentamount_old = Util.null2String(rs.getString("dcurrentamount"));
				String ce = BigDecimalCalculate.floatSubtract(dBBGAmount, dBBGAmount_old);
				String  dcurrentamount_new  =  BigDecimalCalculate.floatAdd(dcurrentamount_old, ce);
				bl =rs1.execute("update "+ysxxtable+" set dBBGAmount="+dBBGAmount+",dsumamount="+dsumamount+",dcurrentamount="+dcurrentamount_new+"  where "+sqlWhere);

			}else{
				bl =rs.execute("insert into "+ysxxtable+"(iyear,cbudgettype,centitycode,centityname,cexpenseitemcode,cexpenseitemname,citemcode,citemname,dBBGAmount,dsumamount,dcurrentamount,formmodeid,modedatacreater) " +
						" values('"+iyear+"','"+cbudgettype+"','"+centitycode+"','"+centityname+"','"+cexpenseitemcode+"','"+cexpenseitemname+"','"+citemcode+"','"+citemname+"',"+dBBGAmount+","+dsumamount+","+dcurrentamount+","+ysxxmodeid+",1)");

			}
			if(bl){
				type="1";
			}else{
				type="0";
			}
			rs.execute("insert into updateExpenseLog(iyear,cbudgettype,centitycode,centityname,cexpenseitemcode,cexpenseitemname,citemcode,citemname,dBBGAmount,dsumamount,dcurrentamount,opdate,type) " +
					" values('"+iyear+"','"+cbudgettype+"','"+centitycode+"','"+centityname+"','"+cexpenseitemcode+"','"+cexpenseitemname+"','"+citemcode+"','"+citemname+"',"+dBBGAmount+","+dsumamount+","+dcurrentamount+",'"+TimeUtil.getCurrentTimeString()+"','"+type+"')");
		}
		return bl;
	}
}
